import  pymysql,faker
def myConn():
    try:
        conn=pymysql.connect(user='root',
                             password='!QAZ2wsx',
                             database='mydb',
                             host='192.168.79.129',
                             port=3306,
                             charset='utf8'
                             )
        print('---数据库连接成功---')
        return conn
    except Exception:
        print('---数据库连接失败---')

def myexecute(conn,sql):
    try:
        cursor1 = conn.cursor()
        cursor1.execute(sql)
        conn.commit()
        rall = cursor1.fetchall()  # 返回所有数据
        # r = cursor1.fetchone() # 返回其中一条数据
        print(rall)
    except Exception:
        print("---数据库执行失败---")

def myclose(conn):
    try:
        conn.close()
    except Exception:
        print("----数据库关闭失败----")

def createable(sql):
    conn = myConn()
    myexecute(conn, sql)
    myclose(conn)

def insertinfo(sql):
    conn = myConn()
    myexecute(conn, sql)
    myclose(conn)

def selectinfo(sql):
    conn = myConn()
    myexecute(conn, sql)
    myclose(conn)
    print('执行成功')

def updateinfo(sql):
    conn = myConn()
    myexecute(conn, sql)
    myclose(conn)

def deleteinfo(sql):
    conn = myConn()
    myexecute(conn, sql)
    myclose(conn)


if __name__=='__main__':
    sql1 = "create table if not exists employee(id int,name char(100),sex char(3),address char(200))"
    createable(sql1)
    f = faker.Faker(locale="zh-CN")
    info = f.profile()
    sql2 = f"insert into employee values(1,'{info.get("name")}','{info.get("sex")}','{info.get("address")}')"
    insertinfo(sql2)
    sql3='select * from teachers where id =3'
    selectinfo(sql3)
    sql4="update teachers set name='tyx' where age=25"
    updateinfo(sql4)
    sql5="delete from teachers where age=32"
    updateinfo(sql5)
    sql6 = 'select * from students s join teachers t on t.id = s.teacher_id and s.teacher_id = 1'
    selectinfo(sql6)
